<?php
	//include("../connect/connect_dsccmis.php");
	$sql = "drop table if exists mis_event_trend;";
	$rs=mysql_query($sql);
	$sql="create table mis_event_trend as select a.prov,a.ym,a.cdscc,b.c from (select prov,concat(yyyy,mon)as ym,cdscc  from  mis_const_prov,mis_const_year,mis_const_month,mis_const_cdscc) a left join(select left(a.evnplcid,2) as prov,left(a.evndte,6) as ym,cdscc,count(*) as c from mis_viewevent a inner join dscc.TempEventAndCaseOfEvent b on a.evnid=b.evnid where left(a.evnplcid,2) in ('94','95','96') or left(a.evnplcid,4) in ('9003','9004','9005','9006') group by left(a.evnplcid,2),left(a.evndte,6),cdscc) b on a.prov=b.prov and a.ym=b.ym and a.cdscc=b.cdscc";

	$rs=mysql_query($sql);
	
	//เปลี่ยนค่าว่างของ c ให้เป็น 0
	$sql = "update mis_event_trend set c=0 where c is null";
	$rs=mysql_query($sql);
	
	//mysql_close($c);
	echo "<br>complete create mis_event_trend on dsccmis";
?>